package com.seafood.repository;

import com.seafood.entity.Order;
import com.seafood.entity.User;
import org.springframework.data.domain.Page;
import org.springframework.data.domain.Pageable;
import org.springframework.data.jpa.repository.JpaRepository;
import org.springframework.data.jpa.repository.Query;
import org.springframework.data.repository.query.Param;
import org.springframework.stereotype.Repository;

import java.math.BigDecimal;
import java.time.LocalDateTime;
import java.util.List;
import java.util.Optional;

/**
 * 订单数据访问层
 */
@Repository
public interface OrderRepository extends JpaRepository<Order, Long> {
    
    /**
     * 根据订单号查找订单
     */
    Optional<Order> findByOrderNumber(String orderNumber);
    
    /**
     * 根据用户查找订单
     */
    Page<Order> findByUserOrderByCreatedAtDesc(User user, Pageable pageable);
    
    /**
     * 根据用户ID查找订单
     */
    Page<Order> findByUserIdOrderByCreatedAtDesc(Long userId, Pageable pageable);
    
    /**
     * 根据订单状态查找订单
     */
    Page<Order> findByStatusOrderByCreatedAtDesc(Order.OrderStatus status, Pageable pageable);
    
    /**
     * 根据用户和状态查找订单
     */
    Page<Order> findByUserAndStatusOrderByCreatedAtDesc(User user, Order.OrderStatus status, Pageable pageable);
    
    /**
     * 查找指定时间段内的订单
     */
    @Query("SELECT o FROM Order o WHERE o.createdAt BETWEEN :startTime AND :endTime ORDER BY o.createdAt DESC")
    List<Order> findByCreatedAtBetween(@Param("startTime") LocalDateTime startTime, 
                                      @Param("endTime") LocalDateTime endTime);
    
    /**
     * 统计用户订单数量
     */
    @Query("SELECT COUNT(o) FROM Order o WHERE o.user.id = :userId")
    Long countByUserId(@Param("userId") Long userId);
    
    /**
     * 统计指定状态的订单数量
     */
    Long countByStatus(Order.OrderStatus status);
    
    /**
     * 查找待支付超时的订单
     */
    @Query("SELECT o FROM Order o WHERE o.status = :status AND o.createdAt < :expireTime")
    List<Order> findExpiredOrders(@Param("status") Order.OrderStatus status, 
                                 @Param("expireTime") LocalDateTime expireTime);
    
    /**
     * 根据用户ID统计订单总金额
     */
    @Query("SELECT SUM(o.finalAmount) FROM Order o WHERE o.user.id = :userId")
    BigDecimal sumFinalAmountByUserId(@Param("userId") Long userId);
    
    /**
     * 根据用户ID和订单状态统计订单数量
     */
    long countByUserIdAndStatus(Long userId, Order.OrderStatus status);
    
    /**
     * 根据用户ID和多个订单状态统计订单数量
     */
    @Query("SELECT COUNT(o) FROM Order o WHERE o.user.id = :userId AND o.status IN :statuses")
    long countByUserIdAndStatusIn(@Param("userId") Long userId, @Param("statuses") List<Order.OrderStatus> statuses);
}